﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.AssetRegister;
using IsmsExplorer.Infrastructure.Helper;
using IsmsExplorer.Model.Projects;
using IsmsExplorer.Infrastructure.RepositoryFramework;
using IsmsExplorer.Model.RiskRegister;

namespace IsmsExplorer.Infrastructure.Repositories {
    internal class AssetRepository : SqlServerRepositoryBase<Asset>, IAssetRepository {
        #region Data
        #endregion

        #region Constructors
        public AssetRepository(IUnitOfWork unitOfWork) : base(unitOfWork) {
        }

        public AssetRepository() : this(null) {
        }
        #endregion

        #region IAssetRepository members

        public Asset FindBy(string name, Project project) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntityFromSql(sb.AppendFormat(" WHERE A.{0}={1} AND A.{2}={3}", 
                AssetFactory.FieldNames.Name, DataHelper.GetSqlValue(name),
                AssetFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(project.ID))
                .Append(";").ToString());
        }

        public IList<Asset> FindBy(Project project) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntitiesFromSql(sb.AppendFormat(" WHERE A.{0}={1}",                
                AssetFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(project.ID))
                .Append(";").ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Need to insert callbacks to build child objects: Project, Owner, Location, Category.
            this.ChildCallbacks.Add(AssetFactory.FieldNames.ProjectID, this.AppendProject);
            this.ChildCallbacks.Add(AssetFactory.FieldNames.OwnerRoleID, this.AppendOwner);
            this.ChildCallbacks.Add(AssetFactory.FieldNames.LocationID, this.AppendLocation);
            this.ChildCallbacks.Add(AssetFactory.FieldNames.CategoryID, this.AppendCategory);
        }
        #endregion

        #region Child object callbacks
        private void AppendProject(Asset asset, object projectID) {
            IProjectRepository r = RepositoryFactory.GetRepository<IProjectRepository, Project>();
            asset.Project = r.FindBy(projectID);
        }
        private void AppendOwner(Asset asset, object ownerRoleID) {
            IRoleRepository r = RepositoryFactory.GetRepository<IRoleRepository, Role>();
            asset.Owner = r.FindBy(ownerRoleID);
        }
        private void AppendLocation(Asset asset, object locationID) {
            ILocationRepository r = RepositoryFactory.GetRepository<ILocationRepository, Location>();
            asset.Location = r.FindBy(locationID);
        }
        private void AppendCategory(Asset asset, object assetCategoryID) {
            IAssetCategoryRepository r = RepositoryFactory.GetRepository<IAssetCategoryRepository, AssetCategory>();
            asset.Category = r.FindBy(assetCategoryID);
        }
        #endregion

        #region BuildEntityFromSql override (to get the Threats associated with Asset).

        protected override Asset BuildEntityFromSql(string sql) {
            Asset asset = base.BuildEntityFromSql(sql);
            if (asset != null) {
                // Now get Threats and assign them to Asset.
                IThreatRepository r = RepositoryFactory.GetRepository<IThreatRepository, Threat>();
                IList<Threat> threats = r.FindBy(asset);
                foreach (Threat t in threats) {
                    asset.AddThreat(t);
                }
            }
            return asset;
        }
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1},{2},{3},{4},{5},{6} FROM [Assets] A",
                AssetFactory.FieldNames.ID,
                AssetFactory.FieldNames.Name,
                AssetFactory.FieldNames.CategoryID,
                AssetFactory.FieldNames.LocationID,
                AssetFactory.FieldNames.OwnerRoleID,
                AssetFactory.FieldNames.ProjectID,
                AssetFactory.FieldNames.ValueCIA);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(" WHERE ").Append(AssetFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion

        #region UnitOfWork implementation
        protected override void PersistDeletedItem(Asset item) {
            string sql = string.Format(@"DELETE FROM [Assets] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Asset item) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Assets]({0},{1},{2},{3},{4},{5}) VALUES ({6},{7},{8},{9},{10},{11}); SELECT SCOPE_IDENTITY();",
                AssetFactory.FieldNames.Name,
                AssetFactory.FieldNames.CategoryID,
                AssetFactory.FieldNames.LocationID,
                AssetFactory.FieldNames.OwnerRoleID,
                AssetFactory.FieldNames.ProjectID,
                AssetFactory.FieldNames.ValueCIA,
                DataHelper.GetSqlValue(item.Name),
                DataHelper.GetSqlValue(item.Category.ID),
                DataHelper.GetSqlValue(item.Location.ID),
                DataHelper.GetSqlValue(item.Owner.ID),
                DataHelper.GetSqlValue(item.Project.ID),
                DataHelper.GetSqlValue(item.ValueCIA)
                );
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            //this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }

        protected override void PersistUpdatedItem(Asset item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [Assets] SET ");
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.Name, DataHelper.GetSqlValue(item.Name));
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.CategoryID, DataHelper.GetSqlValue(item.Category.ID));
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.LocationID, DataHelper.GetSqlValue(item.Location.ID));
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.OwnerRoleID, DataHelper.GetSqlValue(item.Owner.ID));
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(item.Project.ID));
            sb.AppendFormat("{0}={1},", AssetFactory.FieldNames.ValueCIA, DataHelper.GetSqlValue(item.ValueCIA));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class AssetRepository : SqlServerRepositoryBase<Asset>, IAssetRepository

}
